import numpy as np
import pymysql.cursors

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='1234',
                             database='soft',
                             cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor()
sql = " SELECT * FROM cars"
cursor.execute(sql)
result = cursor.fetchall()

# 初始化特征和标签列表
X=[]
Y=[]
Z=[]

# 标记字典
brandMark={"宝马":1,"奔驰":2,"比亚迪":3,"大众":4,"红旗":5,"路虎":6,"奇瑞":7,"沃尔沃":8}
typeMark={"轿车":1,"SUV":2,"MPV":3}
gearboxMark={"手动":1,"自动":2}
energyMark={"汽油":1,"新能源":2,"轻混系统":3}
displacementMark={"1.1-1.6L":1,"1.7-2.0L":2,"2.1-2.5L":3,"2.6-3.0L":4,"3.1-4.0L":5,"4.0L以上":6}
horsepowerMark={"101-150" :1,"151-200" :2,"201-250" :3,"251-300" :4,"301-400" :5,"400以上" :6}
cylindersMark={"3缸":1,"4缸":2,"6缸":3,"8缸":4,"8缸以上":5}
airinflowMark={"自然吸气":1,"涡轮增压":2,"机械增压":3}
fuelMark={"92号":1,"95号":2,"不使用":3}


# 遍历数据集，构建特征和标签
for item in result:
    single=[]
    single.append(brandMark[item["brand"]])
    single.append(typeMark[item["type"]])
    single.append(gearboxMark[item["gearbox"]])
    single.append(energyMark[item["energy"]])
    single.append(displacementMark[item["displacement"]])
    single.append(horsepowerMark[item["horsepower"]])
    single.append(cylindersMark[item["cylinders"]])
    single.append(airinflowMark[item["airinflow"]])
    single.append(fuelMark[item["fuel"]])

    X.append(single)
    Y.append(item["minprice"])
    Z.append(item["maxprice"])

# 将特征和标签转换为numpy数组
X=np.array(X)
Y=np.array(Y)

# 计算线性回归的参数theta
theta1=np.linalg.pinv(X.T.dot(X)).dot(X.T).dot(Y)
theta2=np.linalg.pinv(X.T.dot(X)).dot(X.T).dot(Z)

f=open("linear.py","w")
f.write("linear1="+str(list(theta1))+"\n"+"linear2="+str(list(theta2)))